﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using mitasoft.Component.Dapper;
using mitasoft.Component.Models;
using mitasoft.Component.Models.Blog;
using mitasoft.Component.Services.Interfaces;

namespace mitasoft.Component.Services
{

    public class BlogService : BaseService, IBlogService
    {

        #region fields

        #endregion

        #region ctor

        public BlogService()
        {

        }

        #endregion

        #region Category

        public CategoryInfo AddCategory(string title)
        {
            return AddCategory(title, null);
        }
        public CategoryInfo AddCategory(string title, IDbTransaction transaction)
        {
            var existingCategory = Connection
                .Query<CategoryInfo>("select * from CategoryInfo where title = @Title",
                    param: new { Title = title }, buffered: false, transaction: transaction);

            if (existingCategory.Count() == 0)
            {
                var newCategory = new CategoryInfo
                {
                    Description = title,
                    Title = title
                };

                Connection.Insert(newCategory, transaction);
                return newCategory;
            }
            return null;
        }

        public CategoryInfo[] GetCategories()
        {
            return Connection.Query<CategoryInfo>("select * from CategoryInfo", buffered: false).ToArray();
        }

        public IEnumerable<CategoryTag> RetriveCategoryTags()
        {
            var categoryTags = new List<CategoryTag>();

            var distinctCategories = Connection.Query<CategoryInfo>("select * from CategoryInfo", buffered: false).ToArray();
            var associatedCategories =
                Connection.Query<string>(
                    @"select ci.Title from PostCategory as pc 
                        inner join CategoryInfo as ci 
                            on pc.CategoryId = ci.Id");

            foreach (var tag in distinctCategories)
            {
                var tagCount = associatedCategories.Count(x => x.Equals(tag.Title));
                //var width = (tagCount / associatedCategories.Count()) % factor;
                var width = tagCount;

                if (width > 0)
                {
                    categoryTags.Add(new CategoryTag
                    {
                        Title = tag.Title,
                        Width = width
                    });
                }
            }

            return categoryTags.OrderByDescending(x => x.Width);
        }

        private CategoryInfo GetCategoryByTitle(string title, IDbTransaction transaction = null)
        {

            var existingCategory = Connection
                .Query<CategoryInfo>("select * from CategoryInfo where title = @Title",
                    new { Title = title }, buffered: false, transaction: transaction);

            if (existingCategory.Count() > 0)
                return existingCategory.ElementAt(0);

            return null;
        }

        #endregion

        #region Post

        public PostInfo NewPost(string title, string description, DateTime createdDate, bool publish, IEnumerable<string> categories)
        {

            SqlTransaction transaction = Connection.BeginTransaction();

            PostInfo post = new PostInfo();
            post.Title = title;
            post.Description = description;
            post.DateCreated = (createdDate == default(DateTime)) ? DateTime.Now : createdDate;
            post.Publish = publish;

            // new post here
            Connection.Insert<PostInfo>(post, transaction);

            if (categories.Count() == 0)
            {
                return null;
            }

            categories.ToList().ForEach(categoryTitle =>
            {
                AssociateCategoryPost(categoryTitle, post, transaction);
            });

            transaction.Commit();
            return post;
        }

        public PostInfo GetPost(int id)
        {
            return GetPost(id, null);
        }
        public PostInfo GetPost(int id, IDbTransaction transaction)
        {

            var post = Connection.Get<PostInfo>(id, transaction: transaction);

            var postCategories =
                Connection.Query<string>(
                    @"select ci.Title from PostCategory as pc 
                        inner join CategoryInfo as ci 
                            on pc.CategoryId = ci.Id 
                      where PostId=@Id",
                                       new { Id = id }, buffered: false, transaction: transaction);

            post.Categories = postCategories.ToArray();
            return post;
        }

        public PagedData<PostInfo> GetRecentPostPaginated(int skip, int take)
        {
            int total = 0;

            var p = new DynamicParameters();
            p.Add("@Skip", skip);
            p.Add("@Take", take);
            p.Add("@Total", total, DbType.Int32, ParameterDirection.Output);

            var result = Connection
                .Query<PostInfo>("Pwp_GetRecentPosts",
                    param: p,
                    commandType: CommandType.StoredProcedure);

            total = p.Get<int>("@Total");

            return new PagedData<PostInfo> { Data = result, Total = total };
        }

        public PostInfo[] GetPosts()
        {
            return GetPosts(null);
        }
        public PostInfo[] GetPosts(IDbTransaction transaction)
        {
            var allPosts = new List<PostInfo>();
            foreach (var post in Connection.Query<PostInfo>("select * from PostInfo order by datecreated desc"))
            {
                var postCategories =
                    Connection.Query<string>(
                        @"select ci.Title from PostCategory as pc 
                            inner join CategoryInfo as ci 
                                on pc.CategoryId = ci.Id where PostId=@Id",
                                           new { Id = post.Id }, buffered: false, transaction: transaction);

                post.Categories = postCategories.ToArray();
                allPosts.Add(post);
            }

            return allPosts.ToArray();
        }

        public PostInfo UpdatePost(int id, string title, string description, DateTime createdDate, bool publish, IEnumerable<string> categories)
        {

            SqlTransaction transaction = Connection.BeginTransaction();

            var existingPost = GetPost(id, transaction: transaction);
            existingPost.Title = title;
            existingPost.Description = description;
            existingPost.DateCreated = (createdDate == default(DateTime)) ? DateTime.Now : createdDate;
            existingPost.Publish = publish;

            // for removed categories, remove post associations
            var removedCategories = existingPost.Categories.Except(categories).ToList();
            foreach (string categoryTitle in removedCategories)
            {
                DeletePostCategory(categoryTitle, transaction);
            }

            // for new categories, add new post associations
            var addedCategories = categories.Except(existingPost.Categories).ToList();
            foreach (string categoryTitle in addedCategories)
            {
                AssociateCategoryPost(categoryTitle, existingPost, transaction);
            }

            // update post
            Connection.Update(existingPost, transaction: transaction);

            // save
            transaction.Commit();
            return existingPost;

        }

        private void DeletePostCategory(string title, IDbTransaction transaction = null)
        {

            var category = GetCategoryByTitle(title, transaction);

            Connection.Execute("DELETE FROM PostCategory WHERE CategoryId=@CategoryId",
                param: new { CategoryId = category.Id }, transaction: transaction);
        }
        private void AssociateCategoryPost(string categoryTitle, PostInfo post, IDbTransaction transaction = null)
        {

            // get categories already saved and 
            // associate with the post
            CategoryInfo postCategory = GetCategoryByTitle(categoryTitle, transaction);

            if (postCategory != null)
            {

                // new association between post and categories
                Connection.Insert<PostCategory>(new PostCategory
                {
                    CategoryId = postCategory.Id,
                    PostId = post.Id
                }, transaction: transaction);
            }
        }

        #endregion
    }
}
